import json, duckdb, os, uuid
from utils.path_utils import get_project_root
from flask import Blueprint, request, jsonify, Response
from config import ini_op
from time import strftime
from service.rpa import rpa_run
from service.sjcl import sjcl_xmzd
from database.duckdb.duckdb_base import get_connection

# app = Flask(__name__)
# 创建 Blueprint
rpa_xmzdsc_blueprint = Blueprint('xmzdsc', __name__)


# 项目字典筛选配置
@rpa_xmzdsc_blueprint.route('/xmzd/fiyearlist', methods=['GET', 'POST'])
def xmzd_fiyearlist():
    conn = get_connection()
    rows = conn.execute("select fiyear from t_api_xm group by fiyear order by fiyear desc").fetchall()
    conn.close()
    data = []
    for row in rows:
        data.append(str(row[0]))
    # 返回JSON响应
    data = json.dumps({"list": data}, ensure_ascii=False)
    return Response(data, content_type="application/json") 
@rpa_xmzdsc_blueprint.route('/xmzd/drsjlist', methods=['GET', 'POST'])
def xmzd_drsjlist():
    conn = get_connection()
    rows = conn.execute("select esun_api_update from t_api_xm group by esun_api_update order by esun_api_update desc").fetchall()
    conn.close()
    data = []
    for row in rows:
        data.append(str(row[0]))
    # 返回JSON响应
    data = json.dumps({"list": data}, ensure_ascii=False)
    return Response(data, content_type="application/json") 

# 项目字典上传配置
@rpa_xmzdsc_blueprint.route('/xmzdsc', methods=['GET', 'POST'])
def rpa_xmzdsc():
    data = ini_op.getjson('xmzd_config')
    data['basekjnd'] = ini_op.getinivalue('basic_config', 'kjnd')[0]
    # 返回JSON响应
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 项目字典上传预览
@rpa_xmzdsc_blueprint.route('/xmzdsc/yl', methods=['GET', 'POST'])
def rpa_xmzdsc_yl():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM t_yl_xm")
    # 获取所有行
    rows = cursor.fetchall()
    # 获取列名
    column_names = [description[0] for description in cursor.description]
    # 将行数据转换为字典列表，并转换 UUID 为字符串
    log = []
    for row in rows:
        row_dict = dict(zip(column_names, row))
        for key, value in row_dict.items():
            if isinstance(value, uuid.UUID):  # 检查是否是 UUID 类型
                row_dict[key] = str(value)  # 转换为字符串
        log.append(row_dict)
    conn.close()
    # 返回JSON响应
    data = {
        "rows": log
    }
    # print(data)
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")

# 项目字典删除
@rpa_xmzdsc_blueprint.route('/xmzdsc/delete', methods=['POST'])
def rpa_xmzdsc_delete():
    dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
    data = request.json
    rows = data['rows']
    for row in rows:
        xmnd = row['年度']
        xmlb = row['项目类别']
        con = get_connection()
        con.execute("DELETE FROM T_API_xm WHERE accountorg = '"+dwid+"' and fiyear = '"+xmnd+"'  and cusitemproperty = '"+xmlb+"'")
        con.close()
    return jsonify({"code": 200, "message": "删除成功"}), 200

@rpa_xmzdsc_blueprint.route('/xmzdsc/deleteall', methods=['POST'])
def rpa_xmzdsc_deleteall():
    dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
    con = get_connection()
    con.execute("DELETE FROM T_API_xm WHERE accountorg = '" + dwid + "'")
    con.close()
    return jsonify({"code": 200, "message": "删除成功"}), 200

# 项目字典上传统计
@rpa_xmzdsc_blueprint.route('/xmzdsc/tj', methods=['GET', 'POST'])
def rpa_xmzdsc_tj():
    dwbh, dwid, zbid, zbbh, kjnd = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh', 'kjnd')
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""SELECT uuid() id, '项目字典' as 类别, cusitemproperty as 项目类别, fiyear as 年度, count(*) as 数量 
                        FROM T_API_xm 
                       WHERE accountorg = ?
                       group by 年度, 项目类别 
                       order by 年度, 项目类别""", [dwid])
    # 获取所有行
    rows = cursor.fetchall()
    # 获取列名
    column_names = [description[0] for description in cursor.description]
    # 将行数据转换为字典列表，并转换 UUID 为字符串
    log = []
    for row in rows:
        row_dict = dict(zip(column_names, row))
        for key, value in row_dict.items():
            if isinstance(value, uuid.UUID):  # 检查是否是 UUID 类型
                row_dict[key] = str(value)  # 转换为字符串
        log.append(row_dict)
    conn.close()
    # 返回JSON响应
    data = {
        "rows": log
    }
    # print(data)
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 项目字典上传更新配置
@rpa_xmzdsc_blueprint.route('/xmzdsc/update', methods=['GET', 'POST'])
def rpa_xmzdsc_update():
    data = request.json
    basekjnd = data['basekjnd']
    ini_op.opinivalue('basic_config', kjnd=basekjnd)
    ini_op.opinivalue('xmzd_config', **data)
    # 返回JSON响应
    data = json.dumps({"message": "success"}, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 项目字典上传运行
@rpa_xmzdsc_blueprint.route('/xmzdsc/run', methods=['GET', 'POST'])
def rpa_xmzdsc_run():
    data = request.json
    timenow = str(strftime("%Y%m%d%H%M%S"))
    ini_op.opinivalue('xmzd_config', **data)
    if data['mode'] == '机器人取数':
        rpa_run.run('xmzdsc')
    else:
        dwbh, dwid, xmtx_id, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'xmtx_id', 'zbbh')
        sjcl_xmzd.run(dwbh, dwid, xmtx_id, zbbh, data['kjnd'], data['filepath'], timenow)
    # 返回JSON响应
    data = json.dumps({"message": "success"}, ensure_ascii=False)
    return Response(data, content_type="application/json")